#_*_coding:utf-8_*_

# Copyright (c) 2012 crunch <hymm91@gmail.com>
# All rights reserved

#$Id $#
__author__ = 'crunch <hymm91@gmail.com>'
__version__ = '$Revision: 45 $'

import MySQLdb
import sys
import os,types

import pdb

#以id区间方式查询，适合主键id连续性较好的数据,为默认选项
range_section = 1
#以limit 固定增量方式查询，适合主键id存在较大空段的数据，避免不必要的空循环
range_limit   = 2
    
def escape_string(val):
    if not val:
        return ''
    return MySQLdb.escape_string(val)

class dbclass:
    def __init__(self,  source):
        self._conn = None
        self._cursor = None
        self._source = source
        
    def connect(self, charset='gbk', commit=True):
        db_arg = self._source
        try:
            self._conn = MySQLdb.connect(db_arg['sql_host'], db_arg['sql_user'], db_arg['sql_pass'], \
                db_arg['sql_db'], db_arg['sql_port'], charset=charset, use_unicode=False)
            self._cursor = self._conn.cursor(MySQLdb.cursors.DictCursor)
            #self._cursor = self._conn.cursor(MySQLdb.cursors.Cursor)
            if commit:
                self._cursor.execute("set autocommit=1;")
        except MySQLdb.Error , e:
            return (-1, e)
        
        return (0, None)

    def escape_string(self, val):
        return MySQLdb.escape_string(val)

 
    def query(self, sql, param=None):
        ver = MySQLdb.version_info
        ver = ver[0] * 100 + ver[1] * 10 + ver[2]

        #版本1.2.1之后无须转换
        if ver <= 121:
            if not isinstance(sql, unicode):
                charset = self._conn.character_set_name()
                sql = sql.decode(charset, 'ignore')

        try:
            if not param:
                self._cursor.execute(sql)
            else:
                self._cursor.execute(sql, param)
            #infos = self._cursor.fetchall()
        except MySQLdb.Error , e:
            if e[0] == 2006:    
                self.connect()
            return (-1, e)
        except UnicodeDecodeError, e:
            return (-1, e)
        
        infos = self._cursor.fetchall()


        return (0, infos)
                
           
    def commit(self):
        try:
            self._conn.commit()
        except Exception, e:
            return (e[0], e)

        return 0, None

    def close(self):
        try:
            if self._cursor:
                self._cursor.close()
                self._cursor = None
            if self._conn:
                self._conn.close()
                self._conn = None
        except:
            pass

def exec_sql(who, sql, param=None, pre={}, get_lastid=False, affected_rows = False, charset='gbk', commit=True):
    info = dbclass(who)
    res, desc = info.connect(charset=charset, commit=commit)
    if res != 0:
        return (res, desc)
    res = 0
    for v in pre:
        res, desc = info.query(v)
        if res != 0:
            return (res, desc)

    res, desc = info.query(sql, param)
    lastid = 0
    if res == 0 and get_lastid:
        res, tmp = info.query('SELECT LAST_INSERT_ID() as lastid;')
        if res == 0 and len(tmp) > 0:
            lastid = tmp[0]['lastid']
    if res == 0 and affected_rows:
        res, tmp = info.query('SELECT ROW_COUNT() as affected_rows;')
        if res == 0 and len(tmp) > 0:
            lastid = tmp[0]['lastid']

    #if res == 0:
    #    res, commit_desc = info.commit()
    #    if res != 0:
    #        return res, commit_desc

    info.close()
    if (get_lastid or affected_rows) and res == 0:
        return (res, (desc, lastid))
    return (res, desc)

def query_range(who, table, field="*", idfield="id", step=10000, begin=0, end=0, _sql='', reserver=False, charset='gbk', range_type=range_section):
    if range_type == 1:
        return query_range_sec(who, table, field, idfield, step, begin, end, _sql, reserver, charset)
    else:
        return query_range_limit(who, table, field, idfield, step, begin, end, _sql, charset)

def query_range_sec(who, table, field="*", idfield="id", step=10000, begin=0, end=0, _sql='', reserver=False, charset='gbk'):
    res = 0
    if begin <= 0 or end <= 0:
        idx = idfield.find('.')
        tmpfield = idfield
        if idx:
            tmpfield = idfield[idx + 1:]

        sql = 'select MIN(%s), MAX(%s) from %s' % (tmpfield, tmpfield, table)
        res, desc = exec_sql(who, sql, charset=charset)
        if res != 0:
            yield (res, (desc, sql))
        else:
            v = desc[0].values()
            v.sort()
            min_id = v[0]
            if v[1]:
                max_id = v[1] + 1
            else:
                max_id = min_id
    if res == 0:
        if begin > 0:
            min_id = begin 
        if end > 0:
            max_id = end + 1
        cur_id = min_id
        while cur_id < max_id:
            _cur_max = cur_id + step
            if _cur_max > max_id:
                _cur_max = max_id

            if reserver:
                high = max_id - cur_id + 1
                low = max_id - _cur_max + 1
            else:
                low =  cur_id
                high = _cur_max
            if _sql:
                sql = '%s and %s >= %d and %s < %d;' % (_sql, idfield, low, idfield, high)
            else:
                sql = 'select %s from %s where %s >= %d and %s < %d;' % (field, table, idfield, low, idfield, high)
            
            res, desc = exec_sql(who, sql, charset=charset)
            yield (res, (desc, sql))
            cur_id += step

def query_range_limit(who, table, field="*", idfield="id", step=10000, begin=0, end=0, _sql='', charset='gbk'):
    
    res = 0
    if begin <= 0 or end <= 0:
        idx = idfield.find('.')
        tmpfield = idfield
        if idx:
            tmpfield = idfield[idx + 1:]

        sql = 'select MIN(%s), MAX(%s) from %s' % (tmpfield, tmpfield, table)
        res, desc = exec_sql(who, sql, charset=charset)
        if res != 0:
            yield (res, (desc, sql))
        else:
            v = desc[0].values()
            v.sort()
            min_id = v[0]
            if v[1]:
                max_id = v[1] + 1
            else:
                max_id = min_id
    if res == 0:
        if begin > 0:
            min_id = begin 
        if end > 0:
            max_id = end + 1
        low = min_id
        d_index = 0
        while (low < max_id) and (d_index > -1):
            if _sql:
                sql = '%s and %s >= %d order by %s asc limit %d;' % (_sql, idfield, low, idfield, step)
            else:
                sql = 'select %s from %s where %s >= %d order by %s asc limit %d;' % (field, table, idfield, low, idfield, step)
            
            res, desc = exec_sql(who, sql, charset=charset)
            try:
                d_index   = len(desc)-1
                if d_index > -1:
                    if low == desc[d_index][tmpfield]:
                        break
                    else:
                        low = desc[d_index][tmpfield]
            except:
                pass

            yield (res, (desc, sql))

def query_range_update(who, table, sql, idfield="id", step=10000, begin=0, end=0, reserver=False, charset='gbk'):
    res = 0
    if begin <= 0 or end <= 0:
        idx = idfield.find('.')
        tmpfield = idfield
        if idx:
            tmpfield = idfield[idx + 1:]

        tmp = 'select MIN(%s), MAX(%s) from %s' % (tmpfield, tmpfield, table)
        res, desc = exec_sql(who, tmp, charset=charset)
        if res != 0:
            yield (res, (desc, tmp))
        else:
            v = desc[0].values()
            v.sort()
            min_id = v[0]
            max_id = v[1] + 1
    if res == 0:
        if begin > 0:
            min_id = begin
        if end > 0:
            max_id = end + 1
        cur_id = min_id
        while cur_id < max_id:
            _cur_max = cur_id + step
            if _cur_max > max_id:
                _cur_max = max_id

            if reserver:
                high = max_id - cur_id + 1
                low = max_id - _cur_max + 1
            else:
                low =  cur_id
                high = _cur_max

            tmp = '%s and %s >= %d and %s < %d;' % (sql, idfield, low, idfield, high)
            res, desc = exec_sql(who, tmp, charset=charset)
            yield (res, (desc, tmp))
            cur_id += step


def query_range_bykeys(who, table, field="*", keyfield="id", step=10000, keys=list(), charset='gbk'):
    cur = 0
    _max = len(keys)
    _tmp = list()
    if keys:
        if type(keys[0]) == types.IntType or type(keys[0]) == types.LongType:
            _tmp = ['%d' % v for v in keys]
        else:
            _tmp = ['"%s"' % escape_string(v) for v in keys] 
    while cur < _max:
        _cur_max = cur + step
        if _cur_max > len(keys):
            _cur_max = len(keys)
        sql = 'select %s from %s where %s in (%s)' % (field, table, keyfield, ','.join(_tmp[cur:_cur_max]))
        res, desc = exec_sql(who, sql, charset=charset)
        yield (res, (desc, sql))
        cur+= step

        
if __name__ == '__main__':
    pass
    print "\\"
    sql = "1)2004年10月20日下午17:00左右伤者当事人在上班途中騎自行車行駛到龍崗植物園鵬達門診\北段時﹐被身後同向行駛的機動車撞傷在地。伤者當時不醒人事﹐醒後在後腦右側大量出血的情況下﹐勉強從地面爬起並在鵬達門診\附近徘徊走動﹐隨後門診\部醫生看傷者出血過多﹐神智不清﹐便果斷給傷者縫紮傷口並報警。事故發生後对方当事人駕駛車輛逃離事故現場。2)伤者在门诊治疗期间所交付的费用发票被盗，伤者家属已报警。"
    sql = '根據以上的描述，對於F律師，你認為我可以要求他賠償嗎？我應該怎樣做？如果自2004年F律師和L律師互相勾結，我可以要求F律師退回所有律師費嗎？由2004年4月至2005年12月，他在那件房地產商業串謀'
    print escape_string(sql.decode('utf8').encode('gbk'))
        
